USE [BMIG_Mensual_POST_CADENA]
GO

/****** Object:  StoredProcedure [dbo].[WASP_M0002_MORA]    Script Date: 11/24/2011 14:34:27 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WASP_M0002_MORA]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[WASP_M0002_MORA]
GO

USE [BMIG_Mensual_POST_CADENA]
GO

/****** Object:  StoredProcedure [dbo].[WASP_M0002_MORA]    Script Date: 11/24/2011 14:34:27 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[WASP_M0002_MORA] 
AS 
BEGIN 
	SELECT	z073b1cta
			,z073b3cta
			,z073b3oper
			,z073b3suc
			,tipo_mora = CASE WHEN z073b1cta = z073b3cta THEN 'P' ELSE 'E' END
	INTO	#temp
	FROM	z073b3
	WHERE	Z073B3Stat = 'S'
	GROUP BY z073b1cta, z073b3cta, z073b3oper, z073b3suc

	SELECT	z073b3cta
			,z073b3oper
			,z073b3suc
			,'E' AS tipo_mora
	INTO	#temp2
	FROM	#temp 
	GROUP BY z073b3cta, z073b3oper, z073b3suc
	HAVING COUNT(*) = 2

	SELECT	z073b3cta AS cta
			,z073b3oper AS oper
			,z073b3suc AS suc
			,tipo_mora 
	INTO	mora
	FROM	#temp AS a 
	WHERE	NOT EXISTS (
			SELECT	*
			FROM	#temp2 AS b 
			WHERE   a.z073b3cta  = b.z073b3cta
			AND		a.z073b3oper = b.z073b3oper
			AND		a.z073b3suc  = b.z073b3suc
			)
	UNION
	SELECT	*
	FROM	#temp2
END

GO


